package com.example;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;
import pojo.Brand;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

//StringD:\S3\JDBC\jdbc-demo01
public class BrandTest {
    @Test
    //查询所有
    //sql "SELECT * FROM tb_brand"
    //参数 不需要
    //结果 resultSet
    public void testSelectAll() throws Exception {
        //1.加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("src/druid.properties"));
        //1.1获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        //2.获取连接
        Connection connection = dataSource.getConnection();
        //3.sql
        String sql = "SELECT * FROM tb_brand";
        //4.设置参数
        //5.执行sql
        //5.1 获取预编译执行对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        //6处理结果
        List<Brand> brands = new ArrayList<>();
        Brand brand = null;
        while (resultSet.next()) {
            Integer id = resultSet.getInt("id");
            String brand_name = resultSet.getString("brand_name");
            String company_name = resultSet.getString("company_name");
            Integer ordered = resultSet.getInt("ordered");
            String description = resultSet.getString("description");
            Integer status = resultSet.getInt("status");
            brand = new Brand(id, brand_name, company_name, ordered, description, status);
            brands.add(brand);

        }
        System.out.println(brands);
        resultSet.close();
        preparedStatement.close();
        connection.close();
        //查看druid.properties路径开头
        //   System.out.println(System.getProperty("user.dir"));
    }

    @Test
    //增加
    //sql INSERT INTO tb_brand VALUES (DEFAULT,？,？,？,？,？)
    //2. 参数：需要，除了id之外的所有参数信息
    //3. 结果：boolean
    public void testAdd() throws Exception {
        String brandName = "香飘飘";
        String companyName = "香飘飘";
        int ordered = 1;
        String description = "绕地球一圈";
        int status = 1;
        //1.连接数据库
        Properties pro = new Properties();
        pro.load(new FileInputStream("src/druid.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(pro);
        Connection conm = dataSource.getConnection();
        //2.sql
        String sql = "INSERT INTO tb_brand VALUES (DEFAULT,?,?,?,?,?)";
        //3.执行sql
        //3.1预编译sql
        PreparedStatement preparedStatement = conm.prepareStatement(sql);
        //3.2设置参数
        preparedStatement.setString(1, brandName);
        preparedStatement.setString(2, companyName);
        preparedStatement.setInt(3, ordered);
        preparedStatement.setString(4, description);
        preparedStatement.setInt(5, status);
        //3.3执行sql
        int i = preparedStatement.executeUpdate();
        //4.处理结果
        if (i > 0) {
            System.out.println("数据插入成功！");
        }else{
            System.out.println("数据插入失败！");
        }
        preparedStatement.close();
        conm.close();
        //
    }
    @Test
    //修改
    //sql UPDATE tb_brand SET brand_name=?,company_name=?,ordered=?,description=?,STATUS=?, WHERE id=?
    //2. 参数：需要所有参数
    //3. 结果：boolean
    public void testUpDate() throws Exception {
        String brandName = "臭飘飘";
        String companyName = "臭飘飘";
        int ordered = 1;
        String description = "绕地球一圈";
        int status = 1;
        int id=5;
        //1.连接数据库
        Properties pro = new Properties();
        pro.load(new FileInputStream("src/druid.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(pro);
        Connection conm = dataSource.getConnection();
        //2.sql
        String sql = "UPDATE tb_brand SET brand_name=?,company_name=?,ordered=?,description=?,STATUS=? WHERE id=?";
        //3.执行sql
        //3.1预编译sql
        PreparedStatement preparedStatement = conm.prepareStatement(sql);
        //3.2设置参数
        preparedStatement.setString(1, brandName);
        preparedStatement.setString(2, companyName);
        preparedStatement.setInt(3, ordered);
        preparedStatement.setString(4, description);
        preparedStatement.setInt(5, status);
        preparedStatement.setInt(6,id);
        //3.3执行sql
        int i = preparedStatement.executeUpdate();
        //4.处理结果
        if (i > 0) {
            System.out.println("数据修改成功！");
        }else{
            System.out.println("数据修改失败！");
        }
        preparedStatement.close();
        conm.close();
    }
    @Test
    //删
    //sql DELETE FROM tb_brand WHERE id=4
    //2. 参数：需要 id
    //3. 结果：boolean
    public void testDelete() throws Exception {
    //
        Properties properties=new Properties();
        properties.load(new FileInputStream("src/druid.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        Connection connection = dataSource.getConnection();

        String sql="DELETE FROM tb_brand WHERE id=?";

        PreparedStatement preparedStatement=connection.prepareStatement(sql);
        preparedStatement.setInt(1,1);
       int i= preparedStatement.executeUpdate();
        //4.处理结果
        if (i > 0) {
            System.out.println("数据修改成功！");
        }else{
            System.out.println("数据修改失败！");
        }
        preparedStatement.close();
        connection.close();
    }
}
